#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
新闻数据模型 (NewsData)
"""

from sqlalchemy import Column, Integer, String, Float, DateTime, Text
from sqlalchemy.sql import func
from datetime import datetime
from typing import List, Optional
import json

from .BaseModel import BaseModel

class NewsData(BaseModel):
    """新闻数据模型"""
    __tablename__ = 'newsdata'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    news_id = Column(String(100), unique=True, nullable=False, comment='新闻唯一ID')
    title = Column(String(500), nullable=False, comment='新闻标题')
    summary = Column(Text, comment='新闻摘要')
    content = Column(Text, comment='新闻正文内容')
    url = Column(String(500), comment='新闻原文链接')
    source = Column(String(100), comment='新闻来源')
    author = Column(String(100), comment='作者')
    publish_time = Column(DateTime, comment='发布时间')
    crawl_time = Column(DateTime, default=datetime.now, comment='爬取时间')
    category = Column(String(100), comment='新闻分类')
    keywords = Column(Text, comment='关键词列表（JSON格式）')
    stock_codes = Column(Text, comment='相关股票代码列表（JSON格式）')
    stock_name = Column(String(100), comment='股票名称')
    forecast_type = Column(String(100), comment='业绩预告类型')
    forecast_indicator = Column(String(100), comment='业绩预告指标')
    forecast_value = Column(String(200), comment='业绩预估值')
    last_year_value = Column(String(200), comment='上年同期值')
    yoy_change = Column(String(100), comment='同比变化百分比范围')
    qoq_change = Column(String(100), comment='环比变化百分比范围')
    change_reason = Column(String(500), comment='变化原因')
    event_type = Column(String(100), comment='事件类型')
    sentiment_score = Column(Float, default=0.0, comment='情感分析得分')
    importance_score = Column(Float, default=0.0, comment='重要性得分')
    
    @classmethod
    def get_session(cls):
        """获取数据库会话"""
        return cls._db_manager.get_stock_data_session()
    
    @classmethod
    def _get_existing_record(cls, session, data):
        """获取现有记录"""
        return session.query(cls).filter(cls.news_id == data.get('news_id')).first()
    
    @classmethod
    def _update_existing_record(cls, existing, data):
        """更新现有记录"""
        for key, value in data.items():
            if hasattr(existing, key) and value is not None:
                setattr(existing, key, value)
    
    @classmethod
    def _to_dict(cls, result):
        """将结果转换为字典"""
        return {
            'id': result.id,
            'news_id': result.news_id,
            'title': result.title,
            'summary': result.summary,
            'content': result.content,
            'url': result.url,
            'source': result.source,
            'author': result.author,
            'publish_time': result.publish_time,
            'crawl_time': result.crawl_time,
            'category': result.category,
            'keywords': result.keywords,
            'stock_codes': result.stock_codes,
            'stock_name': result.stock_name,
            'forecast_type': result.forecast_type,
            'forecast_indicator': result.forecast_indicator,
            'forecast_value': result.forecast_value,
            'last_year_value': result.last_year_value,
            'yoy_change': result.yoy_change,
            'qoq_change': result.qoq_change,
            'change_reason': result.change_reason,
            'event_type': result.event_type,
            'sentiment_score': result.sentiment_score,
            'importance_score': result.importance_score
        }
    
    @classmethod
    def _preprocess_news_data(cls, news_data):
        """预处理新闻数据，确保时间字段格式正确"""
        processed_data = news_data.copy()
        
        # 处理publish_time字段
        publish_time = processed_data.get('publish_time')
        if publish_time:
            if isinstance(publish_time, str):
                try:
                    processed_data['publish_time'] = datetime.strptime(publish_time, '%Y-%m-%d %H:%M:%S')
                except ValueError:
                    try:
                        processed_data['publish_time'] = datetime.strptime(publish_time, '%Y-%m-%d')
                    except ValueError:
                        processed_data['publish_time'] = datetime.now()
            elif isinstance(publish_time, datetime):
                processed_data['publish_time'] = publish_time
            else:
                processed_data['publish_time'] = datetime.now()
        else:
            processed_data['publish_time'] = datetime.now()
        
        # 处理crawl_time字段
        crawl_time = processed_data.get('crawl_time')
        if crawl_time:
            if isinstance(crawl_time, str):
                try:
                    processed_data['crawl_time'] = datetime.strptime(crawl_time, '%Y-%m-%d %H:%M:%S')
                except ValueError:
                    processed_data['crawl_time'] = datetime.now()
            elif isinstance(crawl_time, datetime):
                processed_data['crawl_time'] = crawl_time
            else:
                processed_data['crawl_time'] = datetime.now()
        else:
            processed_data['crawl_time'] = datetime.now()
        
        # 处理keywords字段，确保是JSON字符串
        keywords = processed_data.get('keywords')
        if keywords is not None:
            try:
                if isinstance(keywords, (list, dict)):
                    processed_data['keywords'] = json.dumps(keywords, ensure_ascii=False)
                else:
                    processed_data['keywords'] = str(keywords)
            except Exception:
                processed_data['keywords'] = None
        
        # 确保news_id存在
        if not processed_data.get('news_id'):
            title = processed_data.get('title', '')
            time_str = processed_data['publish_time'].strftime('%Y%m%d%H%M%S')
            processed_data['news_id'] = f"{hash(title)}_{time_str}"
        
        return processed_data
    
    @classmethod
    def save_news_batch(cls, news_list):
        """批量保存新闻数据"""
        processed_news_list = []
        for news_data in news_list:
            processed_news_list.append(cls._preprocess_news_data(news_data))
        
        return cls.save_batch(processed_news_list)
    
    @classmethod
    def get_latest_news(cls, limit=50, category=None, days_back=20):
        """获取最新新闻列表"""
        if not cls._db_manager:
            raise Exception("数据库管理器未初始化")
        
        session = cls.get_session()
        
        try:
            query = session.query(cls)
            
            # 添加时间过滤
            if days_back:
                from datetime import timedelta
                time_threshold = datetime.now() - timedelta(days=days_back)
                query = query.filter(cls.publish_time >= time_threshold)
            
            # 添加分类过滤
            if category:
                query = query.filter(cls.category == category)
            
            # 按发布时间降序排列
            news_list = query.order_by(cls.publish_time.desc()).limit(limit).all()
            
            # 转换为字典列表
            result = []
            for news in news_list:
                news_dict = {
                    'id': news.id,
                    'news_id': news.news_id,
                    'title': news.title,
                    'summary': news.summary,
                    'url': news.url,
                    'source': news.source,
                    'author': news.author,
                    'publish_time': news.publish_time.strftime('%Y-%m-%d %H:%M:%S') if news.publish_time else None,
                    'crawl_time': news.crawl_time.strftime('%Y-%m-%d %H:%M:%S') if news.crawl_time else None,
                    'category': news.category,
                    'stock_name': news.stock_name,
                    'forecast_type': news.forecast_type,
                    'forecast_indicator': news.forecast_indicator,
                    'forecast_value': news.forecast_value,
                    'last_year_value': news.last_year_value,
                    'yoy_change': news.yoy_change,
                    'qoq_change': news.qoq_change,
                    'change_reason': news.change_reason,
                    'keywords': [],
                    'stock_codes': news.stock_codes,
                    'sentiment_score': news.sentiment_score,
                    'importance_score': news.importance_score
                }
                
                # 处理keywords字段
                if news.keywords:
                    try:
                        news_dict['keywords'] = json.loads(news.keywords)
                    except:
                        news_dict['keywords'] = news.keywords if isinstance(news.keywords, list) else []
                
                result.append(news_dict)
            
            from loguru import logger
            logger.info(result)
            return result
            
        finally:
            session.close()
    
    @classmethod
    def search_news(cls, keyword, limit=20):
        """搜索新闻"""
        if not cls._db_manager:
            raise Exception("数据库管理器未初始化")
        
        session = cls.get_session()
        
        try:
            # 在标题和摘要中搜索关键词
            query = session.query(cls).filter(
                (cls.title.ilike(f'%{keyword}%')) | 
                (cls.summary.ilike(f'%{keyword}%'))
            )
            
            news_list = query.order_by(cls.publish_time.desc()).limit(limit).all()
            
            # 转换为字典列表
            result = []
            for news in news_list:
                news_dict = {
                    'id': news.id,
                    'news_id': news.news_id,
                    'title': news.title,
                    'summary': news.summary,
                    'url': news.url,
                    'source': news.source,
                    'author': news.author,
                    'publish_time': news.publish_time.strftime('%Y-%m-%d %H:%M:%S') if news.publish_time else None,
                    'crawl_time': news.crawl_time.strftime('%Y-%m-%d %H:%M:%S') if news.crawl_time else None,
                    'category': news.category,
                    'stock_name': news.stock_name,
                    'forecast_type': news.forecast_type,
                    'forecast_indicator': news.forecast_indicator,
                    'forecast_value': news.forecast_value,
                    'last_year_value': news.last_year_value,
                    'yoy_change': news.yoy_change,
                    'qoq_change': news.qoq_change,
                    'change_reason': news.change_reason,
                    'keywords': [],
                    'stock_codes': news.stock_codes,
                    'sentiment_score': news.sentiment_score,
                    'importance_score': news.importance_score
                }
                
                # 处理keywords字段
                if news.keywords:
                    try:
                        news_dict['keywords'] = json.loads(news.keywords)
                    except:
                        news_dict['keywords'] = news.keywords if isinstance(news.keywords, list) else []
                
                result.append(news_dict)
           
            return result
            
        finally:
            session.close()
    
    @classmethod
    def cleanup_old_news(cls, days_to_keep=30):
        """清理旧新闻数据"""
        if not cls._db_manager:
            raise Exception("数据库管理器未初始化")
        
        session = cls.get_session()
        
        try:
            from datetime import timedelta
            time_threshold = datetime.now() - timedelta(days=days_to_keep)
            
            # 删除旧数据
            deleted_count = session.query(cls).filter(cls.publish_time < time_threshold).delete()
            session.commit()
            
            return deleted_count
            
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()
